Release 10.1A: OpenEdge Data Management:
SQL Development

Working with indexes

An index is a database object used to speed the time in which data is retrieved from a table. Although OpenEdge creates an index for any column to which you assign a unique constraint, you can also explicitly create an index.

Using the CREATE INDEX statement

The CREATE INDEX statement creates an index on one or more columns of a table. You can specify an index in ascending order (ASC) or descending order (DESC).

Note: For a complete description of the CREATE INDEX statement, see OpenEdge Data Management: SQL Reference .

The CREATE INDEX statement uses the following syntax:

Syntax

CREATE [ UNIQUE ] INDEX index_name ON table_name ( { column_name [ ASC | DESC ] } [, ... ] ) [ AREA area_name ] PRO_ACTIVE { ‘N’ | ‘n’ };

The index in this CREATE INDEX example is specified on the single column empno, and is of ascending order on the value of the column, as shown in Example 5–6.

Example 5–6: CREATE INDEX statement

CREATE INDEX idx_emp ON SPORTS.employee (empno ASC) ;

DROP INDEX

Use the DROP INDEX statement to drop a table index. An index can only be dropped from tables with more than one index. The initial index of a table cannot be dropped. Example 5–7 demonstrates the use of a DROP INDEX statement.

Example 5–7: DROP INDEX statement

DROP INDEX idx_cust ON customer;

For information on the use of indexes, see Chapter 6, " OpenEdge SQL Data Manipulation Language."